from abc import ABC, abstractmethod
from enum import StrEnum, auto

from password import Password


# 数据库类型枚举
class DBType(StrEnum):
    ORACLE = 'ORACLE'
    DMDB = 'DMDB'

# 数据存储器接口
class Database(ABC):
    def __init__(self, user, password, dsn):
        """
        初始化 Oracle 数据库连接
        :param user: 用户名
        :param password: 密码
        :param dsn: 数据源名称（如：localhost:1521/XE）
        """
        self.user = user
        self.password = password
        self.dsn = dsn
        self.connection = None
        self.dbtype = DBType.ORACLE

    @abstractmethod
    def connect(self):
        """连接数据库"""
        pass

    @abstractmethod
    def connect(self):
        pass

    @abstractmethod
    def disconnect(self):
        pass

    @abstractmethod
    def execute_sql(self, sql, params=None):
        pass

    @abstractmethod
    def fetch_all(self, sql, params=None):
        pass

    @abstractmethod
    def fetch_one(self, sql, params=None):
        pass

    @abstractmethod
    def call_procedure(self, procedure_name, params=None):
        pass


# Oracle 数据库
class OracleDB(Database):
    def __init__(self, user, password, dsn):
        super().__init__(user, password, dsn)
        self._oracledb = None
        self.dbtype = DBType.ORACLE

    def _import_oracledb(self):
        """延迟导入oracledb模块"""
        if self._oracledb is None:
            import oracledb
            self._oracledb = oracledb

    def connect(self):
        """连接 Oracle 数据库"""
        self._import_oracledb()
        try:
            self.connection = self._oracledb.connect(user=self.user,
                                                     password=self.password,
                                                     dsn=self.dsn)
            print("Connected to Oracle database successfully!")
        except self._oracledb.Error as e:
            print(f"Failed to connect to Oracle database: {e}")
            raise

    def disconnect(self):
        """
        断开与 Oracle 数据库的连接
        """
        self._import_oracledb()
        if self.connection:
            self.connection.close()
            self.connection = None
            print("Disconnected from Oracle database.")

    def __enter__(self):
        """进入 with 块时连接数据库"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """离开 with 块时断开数据库连接"""
        self.disconnect()

    def execute_sql(self, sql, params=None):
        """
        执行 SQL 语句（如 INSERT, UPDATE, DELETE）
        :param sql: SQL 语句
        :param params: SQL 参数（可选）
        :return: 受影响的行数
        """
        self._import_oracledb()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                self.connection.commit()
                print(f"SQL executed successfully: {sql}")
                return cursor.rowcount
        except self._oracledb.Error as e:
            print(f"Failed to execute SQL: {e}")
            self.connection.rollback()
            raise

    def fetch_all(self, sql, params=None):
        """
        查询所有数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（列表形式）
        """
        self._import_oracledb()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                result = cursor.fetchall()
                columns = [desc[0].upper() for desc in cursor.description]
                print(f"Fetched {len(result)} rows.")
                return result, columns
        except self._oracledb.Error as e:
            print(f"Failed to fetch data: {e}")
            raise

    def fetch_one(self, sql, params=None):
        """
        查询单条数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（单条记录）
        """
        self._import_oracledb()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                result = cursor.fetchone()
                columns = [desc[0].upper() for desc in cursor.description]
                print("Fetched one row.")
                return result, columns
        except self._oracledb.Error as e:
            print(f"Failed to fetch data: {e}")
            raise

    def call_procedure(self, procedure_name, params=None):
        """
        调用存储过程
        :param procedure_name: 存储过程名称
        :param params: 存储过程参数（可选）
        """
        self._import_oracledb()
        try:
            with self.connection.cursor() as cursor:
                cursor.callproc(procedure_name, params or ())
                self.connection.commit()
                print(f"Procedure {procedure_name} executed successfully.")
        except self._oracledb.Error as e:
            print(f"Failed to call procedure: {e}")
            self.connection.rollback()
            raise


# DMDB 数据库
class DMDB(Database):
    def __init__(self, user, password, dsn):
        """
        初始化达梦数据库连接参数
        :param user: 用户名
        :param password: 密码
        :param dsn: 连接字符串，格式为 "server:port/dbname"
        """
        super().__init__(user, password, dsn)
        self._dmPython = None
        self.dbtype = DBType.DMDB
        # 解析 DSN 字符串
        try:
            host, dbname = dsn.split('/') if '/' in dsn else (dsn, '')
            server, port = host.split(':') if ':' in host else (host, '5236')
            self.server = server
            self.port = int(port)
            self.dbname = dbname
        except ValueError:
            raise ValueError(
                "Invalid DSN format. Expected 'server:port/dbname'")

    def _import_dmpython(self):
        """延迟导入dmPython模块"""
        if self._dmPython is None:
            import dmPython
            self._dmPython = dmPython

    def connect(self):
        """连接到达梦数据库"""
        self._import_dmpython()
        try:
            self.connection = self._dmPython.connect(user=self.user,
                                                     password=self.password,
                                                     server=self.server,
                                                     port=self.port)
            print("Connected to DM database successfully!")
        except self._dmPython.Error as e:
            print(f"Failed to connect to DM database: {e}")
            raise

    def disconnect(self):
        """断开与达梦数据库的连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
            print("Disconnected from DM database.")

    def __enter__(self):
        """进入 with 块时连接数据库"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """离开 with 块时断开数据库连接"""
        self.disconnect()

    def execute_sql(self, sql, params=None):
        """
        执行 SQL 语句（如 INSERT, UPDATE, DELETE）
        :param sql: SQL 语句
        :param params: SQL 参数（可选）
        :return: 受影响的行数
        """
        self._import_dmpython()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                self.connection.commit()
                print(f"SQL executed successfully: {sql[:50]}...")
                return cursor.rowcount
        except self._dmPython.Error as e:
            print(f"Failed to execute SQL: {e}")
            self.connection.rollback()
            raise

    def fetch_all(self, sql, params=None):
        """
        查询所有数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（列表形式）
        """
        self._import_dmpython()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                result = cursor.fetchall()
                columns = [desc[0].upper() for desc in cursor.description]
                print(f"Fetched {len(result)} rows.")
                return result, columns
        except self._dmPython.Error as e:
            print(f"Failed to fetch data: {e}")
            raise

    def fetch_one(self, sql, params=None):
        """
        查询单条数据
        :param sql: SQL 查询语句
        :param params: SQL 参数（可选）
        :return: 查询结果（单条记录）
        """
        self._import_dmpython()
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                result = cursor.fetchone()
                columns = [desc[0].upper() for desc in cursor.description]
                print("Fetched one row.")
                return result, columns
        except self._dmPython.Error as e:
            print(f"Failed to fetch data: {e}")
            raise

    def call_procedure(self, procedure_name, params=None):
        """
        调用存储过程
        :param procedure_name: 存储过程名称
        :param params: 存储过程参数（可选）
        """
        self._import_dmpython()
        try:
            with self.connection.cursor() as cursor:
                cursor.callproc(procedure_name, params or ())
                self.connection.commit()
                print(f"Procedure {procedure_name} executed successfully.")
        except self._dmPython.Error as e:
            print(f"Failed to call procedure: {e}")
            self.connection.rollback()
            raise

class DatabaseFactory:
    """数据库工厂类"""
    def __init__(self, type=None, user=None, password=None, dsn=None, conn_str=None):
        self.type = type
        self.user = user
        self.password = password
        self.dsn = dsn
        self.conn_str=conn_str
        self.db_instance = None

    def __enter__(self):        
        """进入 with 块时创建并连接数据库"""
        self.db_instance = self.create_database(self.type, self.user,
                                                self.password, self.dsn, self.conn_str)
        self.db_instance.connect()
        return self.db_instance

    def __exit__(self, exc_type, exc_val, exc_tb):
        """离开 with 块时断开数据库连接"""
        if self.db_instance:
            self.db_instance.disconnect()

    @staticmethod
    def create_database(type: DBType, user, password, dsn, conn_str=None):
        """创建数据库实例"""
        if any(not var for var in [user, password, dsn]):
            if conn_str is None:
                raise ValueError("conn_str must be provided")
            auth_part, dsn = conn_str.split('@', 1)
            user, password = auth_part.split('/', 1)
        else:            
            password = Password(password)

        if any(not var for var in [user, password, dsn]):
            raise ValueError("user, password, dsn must be provided")

        if type == DBType.ORACLE:
            return OracleDB(user, password, dsn)
        elif type == DBType.DMDB:
            return DMDB(user, password, dsn)
        else:
            raise ValueError("Unsupported data storage")
        

if __name__ == "__main__":

    with DatabaseFactory(DBType.ORACLE, 'user', 'password',
                        'server:port/dbname') as db:
        db.execute_sql("INSERT INTO TBL_XXX (COL1, COL2) VALUES (:1, :2)",
                       ('value1', 'value2'))
